﻿/*******************************************************************************************
 * Copyright (c) 2009 Alex Robson, Rob Simmons, Craig Israel, and Rachel Twyford
 *
 * Use of this software in any form requires that you have both 
 * read and agreed to the following terms:
 *
 * Permission is hereby granted, free of charge, to any person obtaining
 * a copy of this software and associated documentation files (the "Software"), 
 * to deal in the Software without restriction, including without limitation 
 * the rights to use, copy, modify, merge, publish, distribute, sublicense, 
 * and/or sell copies of the Software, and to permit persons to whom the
 * Software is furnished to do so, subject to the following conditions: 
 *
 * Redistributions of source code must retain the above copyright
 * notice, this list of conditions and the following disclaimer.
 * Redistributions in binary form must reproduce the above copyright notice, 
 * this list of conditions and the following disclaimer in the 
 * documentation and/or other materials provided with the distribution.
 * Neither the name of Nvigorate nor the names of its contributors may 
 * be used to endorse or promote products derived from this software without 
 * specific prior written permission. 
 *
 * THIS SOFTWARE IS PROVIDED "AS IS" AND ANY
 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE 
 * DISCLAIMED. IN NO EVENT SHALL ANY CONTRIBUTOR BE LIABLE FOR ANY
 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; 
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS 
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 * ****************************************************************************************/

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text;
using System.Xml.Linq;
using Nvigorate.Data.Query;
using System.Linq;

namespace Nvigorate.Data
{
    public class MSSQL9Provider : IDatabaseProvider
    {
        #region Private Fields

        private Dictionary<Type, Func<string, object, SqlParameter>> _parameterDictionary =
            new Dictionary<Type, Func<string, object, SqlParameter>>
                {
                    {
                        typeof (short), (name, value) =>
                            {
                                SqlParameter p = new SqlParameter(name, value);
                                p.SqlDbType = SqlDbType.SmallInt;
                                p.Size = 2;
                                return p;
                            }
                        },
                    {
                        typeof (int), (name, value) =>
                            {
                                SqlParameter p = new SqlParameter(name, value);
                                p.SqlDbType = SqlDbType.Int;
                                p.Size = 4;
                                return p;
                            }
                        },
                    {
                        typeof (long), (name, value) =>
                            {
                                SqlParameter p = new SqlParameter(name, value);
                                p.SqlDbType = SqlDbType.BigInt;
                                p.Size = 8;
                                return p;
                            }
                        },
                    {
                        typeof (Single), (name, value) =>
                            {
                                SqlParameter p = new SqlParameter(name, value);
                                p.SqlDbType = SqlDbType.Float;
                                p.Size = 8;
                                return p;
                            }
                        },
                    {
                        typeof (Double), (name, value) =>
                            {
                                SqlParameter p = new SqlParameter(name, value);
                                p.SqlDbType = SqlDbType.Float;
                                p.Size = 8;
                                return p;
                            }
                        },
                    {
                        typeof (decimal), (name, value) =>
                            {
                                SqlParameter p = new SqlParameter(name, value);
                                p.SqlDbType = SqlDbType.Decimal;
                                p.Size = 8;
                                return p;
                            }
                        },
                    {
                        typeof (DateTime), (name, value) =>
                            {
                                SqlParameter p = new SqlParameter(name, value);
                                p.SqlDbType = SqlDbType.DateTime;
                                p.Size = 8;
                                return p;
                            }
                        },
                    {
                        typeof (bool), (name, value) =>
                            {
                                SqlParameter p = new SqlParameter(name, value);
                                p.SqlDbType = SqlDbType.Bit;
                                p.Size = 1;
                                return p;
                            }
                        },
                    {
                        typeof (Enum), (name, value) =>
                            {
                                SqlParameter p = new SqlParameter(name, value);
                                p.SqlDbType = SqlDbType.TinyInt;
                                p.Size = 1;
                                return p;
                            }
                        },
                    {
                        typeof (string), (name, value) =>
                            {
                                SqlParameter p = new SqlParameter(name, value);
                                p.SqlDbType = SqlDbType.VarChar;
                                p.Size = value.ToString().Length;
                                return p;
                            }
                        },
                    {
                        typeof (Guid), (name, value) =>
                            {
                                SqlParameter p = new SqlParameter(name, value);
                                p.SqlDbType = SqlDbType.UniqueIdentifier;
                                p.Size = 16;
                                return p;
                            }
                        }
                };

        private Dictionary<Type, SqlDbType> _typeConversion = 
            new Dictionary<Type, SqlDbType>()
              {
                  {
                      typeof (short), SqlDbType.SmallInt
                      },
                  {
                      typeof (int), SqlDbType.Int
                      },
                  {
                      typeof (long), SqlDbType.BigInt
                      },
                  {
                      typeof (float), SqlDbType.Float
                      },
                  {
                      typeof (double), SqlDbType.Real
                      },

                  {
                      typeof (decimal), SqlDbType.Decimal
                      },
                  {
                      typeof (bool), SqlDbType.Bit
                      },
                  {
                      typeof (short?), SqlDbType.SmallInt
                      },
                  {
                      typeof (int?), SqlDbType.Int
                      },
                  {
                      typeof (long?), SqlDbType.BigInt
                      },
                  {
                      typeof (float?), SqlDbType.Float
                      },
                  {
                      typeof (double?), SqlDbType.Real
                      },
                  {
                      typeof (decimal?), SqlDbType.Decimal
                      },
                  {
                      typeof (bool?), SqlDbType.Bit
                      },
                  {
                      typeof (string), SqlDbType.VarChar
                      },
                  {
                      typeof (char), SqlDbType.Char
                      },
                  {
                      typeof (DateTime), SqlDbType.DateTime
                      },
                  {
                      typeof (Enum), SqlDbType.TinyInt
                      },
                  {
                      typeof (Guid), SqlDbType.UniqueIdentifier
                      }
              };

        private Dictionary<Type, string> _typeDeclaration = 
            new Dictionary<Type, string>()
                {
                    {
                        typeof (short), "SMALLINT"
                        },
                    {
                        typeof (int), "INT"
                        },
                    {
                        typeof (long), "BIGINT"
                        },
                    {
                        typeof (float), "FLOAT(24,8)"
                        },
                    {
                        typeof (double), "REAL(24,8)"
                        },
                    {
                        typeof (decimal), "DECIMAL(24,8)"
                        },
                    {
                        typeof (bool), "BIT"
                        },
                    {
                        typeof (short?), "SMALLINT"
                        },
                    {
                        typeof (int?), "INT"
                        },
                    {
                        typeof (long?), "BIGINT"
                        },
                    {
                        typeof (float?), "FLOAT(24,8)"
                        },
                    {
                        typeof (double?), "REAL(24,8)"
                        },
                    {
                        typeof (decimal?), "DECIMAL(24,8)"
                        },
                    {
                        typeof (bool?), "BIT"
                        },
                    {
                        typeof (string), "VARCHAR(8000)"
                        },
                    {
                        typeof (char), "CHAR"
                        },
                    {
                        typeof (char?), "CHAR"
                        },
                    {
                        typeof (DateTime), "DATETIME"
                        },
                    {
                        typeof (DateTime?), "DATETIME"
                        },
                    {
                        typeof (Enum), "INT"
                        },
                    {
                        typeof (Guid), "UNIQUEIDENTIFIER"
                        }
                };

        #endregion

        public string ParameterPrefix
        {
            get { return "@"; }
        }

        #region IDb Instance Functions

        public IDbCommand GetCommand(string commandText)
        {
            return new SqlCommand(commandText);
        }

        public IDbCommand GetCommand(IQueryObject query)
        {
            throw new System.NotImplementedException();
        }

        public IDbCommand GetParamsForProcedureCommand(string procedureName)
        {
            string sqlQuery =
                @"	SELECT
					Parameter_Mode,
					Parameter_Name,
					Data_Type
				FROM Information_Schema.Parameters
				WHERE Specific_Name = @ProcedureName";

            IDbCommand command = GetCommand(sqlQuery);
            command.Parameters.Add(new SqlParameter("@ProcedureName", procedureName));
            return command;
        }

        public IDbCommand GetParamsForAllProceduresCommand()
        {
            string sqlQuery =
                @"	SELECT
				Routine_Name as Procedure_Name
			FROM Information_Schema.Routines
			Order By Procedure_Name

			SELECT
				Specific_Name as Procedure_Name,
				Parameter_Mode,
				Parameter_Name,
				Data_Type
			FROM Information_Schema.Parameters
			Order By Procedure_Name";

            IDbCommand command = GetCommand(sqlQuery);
            return command;
        }

        public IDbDataParameter GetParameter(string name, object value, ParameterDirection direction)
        {
            Type valueType = value.GetType();
            return GetParameter(name, value, valueType, direction);
        }

        public IDbDataParameter GetParameter(string name, object value, Type target, ParameterDirection direction)
        {
            name = (name.StartsWith(ParameterPrefix)) ? name : ParameterPrefix + name;
            value = value ?? DBNull.Value;
            SqlParameter parameter;
            if (_parameterDictionary.ContainsKey(target))
            {
                parameter = _parameterDictionary[target].Invoke(name, value);
            }
            else if (_parameterDictionary.ContainsKey(target.BaseType))
            {
                parameter = _parameterDictionary[target.BaseType].Invoke(name, value);
            }
            else
            {
                parameter = new SqlParameter(name, value);
            }

            parameter.Direction = direction;
            return parameter;
        }

        public IDbDataParameter GetParameter(string name, object value, string dbType, ParameterDirection direction)
        {
            name = (name.StartsWith(ParameterPrefix)) ? name : ParameterPrefix + name;
            value = value ?? DBNull.Value;
            SqlParameter parameter = new SqlParameter(name, value);
            ;
            parameter.SqlDbType = (SqlDbType) Enum.Parse(typeof (SqlDbType), dbType, true);
            parameter.Direction = direction;
            return parameter;
        }

        public object GetDbTypeFromType(Type type)
        {
            return _typeConversion[type];
        }

        public Type GetTypeFromDbType(SqlDbType dbType)
        {
            var keyValuePair = _typeConversion.FirstOrDefault(kvp => kvp.Value == dbType);
            return keyValuePair.Key;
        }

        public string GetTypeDeclarationFromType(Type type)
        {
            if (_typeDeclaration.ContainsKey(type))
                return _typeDeclaration[type];

            string value = "";
            if (!_typeDeclaration.TryGetValue(type.BaseType, out value))
                value = "VARIANT";
            return value;
        }

        public IDbConnection GetConnection(string connectionString)
        {
            return new SqlConnection(connectionString);
        }

        public IDbConnection GetConnection(IDbConnection sourceConnection)
        {
            return new SqlConnection(sourceConnection.ConnectionString);
        }

        public IParameterize GetParameterizer()
        {
            return new Parameterizer();
        }

        public IQueryVisitor GetQueryVisitor()
        {
            return new MSSQL9Visitor();
        }
        
        public int GetErrorCodeFromDbException(DbException ex)
        {
            var sqlEx = ex as SqlException;
            return sqlEx == null ? -1 : sqlEx.State;
        }

        #endregion
    }
}
